package top.appx.easysql;

import org.apache.log4j.Logger;
import top.appx.easysql.data.DataRow;
import top.appx.easysql.data.DataTable;
import top.appx.easysql.data.PageInfo;
import top.appx.easysql.ex.EasySqlException;

import java.lang.reflect.Method;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by 燎火 on 2015/10/10.
 */
public abstract class BaseDatabase {
    private static Logger Log=Logger.getLogger("easysql");



    protected Connection connection;

    /**
     * 创建(打开)数据库的方法,子类继承实现
     *
     * @return
     */
    protected abstract Connection createConnection();


    /**
     * 关闭数据库的方法,子类实现
     *
     * @return
     */
    protected abstract void closeConnection();

    /**
     * 插入数据后查询插入数据id的方法,子类可覆盖提高性能
     * @return
     */
    protected String AutoIncreSql(String... tablename){
        return "select max(id) from "+tablename[0];
    }


    private Connection getConnection(){
        if(connection == null){
            connection = createConnection();
        }
        return connection;
    }



    /**
     * 关闭(释放当前对象)
     */
    //@Override
    public void close() {
        if (connection != null) {
            closeConnection();
        }
    }

    /**
     * 开始事务
     */
    public void beginTrans(){
        try {
            this.getConnection().setAutoCommit(false);
        } catch (SQLException e) {
            throw new EasysqlException(e);
        }
    }

    /**
     * 提交事务
     */
    public void commitTrans(){
        try{
            this.getConnection().commit();
            this.getConnection().setAutoCommit(true);
        }catch (SQLException e){
            throw new EasysqlException(e);
        }
    }

    /**
     * 回滚事务
     */
    public void rollback(){
        try {
            this.getConnection().rollback();
            this.getConnection().setAutoCommit(true);
        } catch (SQLException e) {
            throw new EasysqlException(e);
        }
    }





    //region 数据库操作帮助方法

    /**
     * 生成PreparedStatement,需要手动释放
     *
     * @param sql
     * @param paramValues
     * @return
     */
    public PreparedStatement createPst(String sql, Object... paramValues) {
        if (EasySqlConfig.showSql) {
            Log.debug("执行:"+sql);
        }
        try {
            PreparedStatement pst = getConnection().prepareStatement(sql);
            for (int i = 0; i < paramValues.length; i++) {
                pst.setObject(i + 1, paramValues[i]);
            }
            return pst;
        } catch (SQLException ex) {
            throw new EasySqlException(ex);
        }
    }
    //endregion 数据库操作帮助方法

    //region    数据库基本方法

    /**
     * 执行sql语句并返回影响的记录数
     * @param sql
     * @param paramValues
     * @return
     */
    public int execute(String sql, Object... paramValues) {
        PreparedStatement pst = null;
        try {
            pst = createPst(sql, paramValues);
            return pst.executeUpdate();
        } catch (SQLException ex) {
            throw new EasySqlException(ex);
        } finally {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e) {
                    throw new EasySqlException(e);
                }
            }
        }
    }


    /**
     * 执行查询语句并返回结果
     * @param sql
     * @param paramValues
     * @return
     */
    public DataTable queryDataTable(String sql, Object... paramValues) {
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            pst = createPst(sql, paramValues);
            rs = pst.executeQuery();
            DataTable dt = new DataTable();
            while (rs.next()) {
                int cc = rs.getMetaData().getColumnCount();
                DataRow dr = new DataRow(dt);
                for (int i = 0; i < cc; i++) {
                    String key = rs.getMetaData().getColumnName(i + 1);
                    Object value = rs.getObject(i + 1);
                    dr.put(key, value);
                }
                dt.getRows().add(dr);
            }
            return dt;
        } catch (SQLException e) {
            throw new EasySqlException(e);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    throw new EasySqlException(e);
                }
            }

            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e) {
                    throw new EasySqlException(e);
                }
            }
        }
    }


    /**
     * 执行查询并根据将数据转换的entityClass对象集合返回
     * @param entityClass
     * @param sql
     * @param paramValues
     * @param <T>
     * @return
     */
    public <T> List<T> queryBySql(Class<T> entityClass, String sql, Object... paramValues) {
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            pst = createPst(sql, paramValues);
            rs = pst.executeQuery();
            return DBUtil.resultToList(rs, entityClass);
        } catch (SQLException e) {
            throw new EasySqlException(e);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    throw new EasySqlException(e);
                }
            }

            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e) {
                    throw new EasySqlException(e);
                }
            }
        }

    }


    /**
     * 分页查询
     * @param sql
     * @param pageInfo
     * @param paramValues
     * @return
     */
    public PageInfo<DataRow> queryPage(String sql,PageInfo pageInfo,Object... paramValues){
        String sqlCount = DBUtil.getCountSql(sql);
        String sqlPage = DBUtil.getLimitString(sql,pageInfo.getPage(),pageInfo.getPageSize());
        pageInfo.setTotal(total(sqlCount,paramValues));
        DataTable dt = queryDataTable(sqlPage,paramValues);
        pageInfo.setData(dt.getRows());
        return pageInfo;
    }

    protected abstract String packMax(String sql,int start,int max);

    public PageInfo<DataRow> queryPageBySqlRestrain(String sql,PageInfo pageInfo,Restrain... restrains){
        List paramValues = new ArrayList();
        StringBuilder sqlB = new StringBuilder(sql);
        DBUtil.transSqlWhere(sqlB,paramValues,restrains);
        return queryPage(sqlB.toString(),pageInfo,paramValues.toArray());
    }

    //public <T> PageInfo<T> queryPageBySql(Class<T> entityClass,String sql )


    /**
     * 查询并返回第一条记录的第一个数据
     * @param sql
     * @param paramValues
     * @return
     */
    public Object queryScalar(String sql, Object... paramValues) {
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            pst = createPst(sql, paramValues);
            rs = pst.executeQuery();
            Object result = null;
            if (rs.next()) {
                result = rs.getObject(1);
            }
            return result;

        } catch (SQLException ex) {
            throw new EasySqlException(ex);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    throw new EasySqlException(e);
                }
            }

            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e) {
                    throw new EasySqlException(e);
                }
            }
        }
    }
    /**
     * 查询数量
     * @param sql
     * @param paramValues
     * @return
     */
    public int total(String sql,Object... paramValues){
        return Integer.parseInt(queryScalar(sql,paramValues).toString());
    }

    //endregion 数据库基本方法



    //region 数据库扩展方法

    /**
     * 根据entityClass与约束条件restrains查询
     * @param entityClass   实体化类
     * @param restrains
     * @param <T>
     * @return
     */
    public <T> List<T> query(Class<T> entityClass, Restrain... restrains) {
        List paramValues = new ArrayList();
        String sql = DBUtil.transSqlWhere(entityClass,paramValues,restrains);
        int start = 0;
        int max = 0;
        for(Restrain restrain : restrains){
            switch (restrain.getRestrainType()){
                case start:
                    start = Integer.parseInt(restrain.getValues()[0].toString());
                    break;
                case max:
                    max = Integer.parseInt(restrain.getValues()[0].toString());
                    break;

            }
        }
        if(start != 0 || max != 0 ){
            sql = packMax(sql,start,max);
        }

        return queryBySql(entityClass, sql.toString(), paramValues.toArray());
    }


    /**
     * 查询数量
     * @param entityClass
     * @param restrains
     * @return
     */
    public int total(Class<?> entityClass,Restrain... restrains){
        List paramValues = new ArrayList();
        String sql = DBUtil.transSqlWhere(entityClass,paramValues,restrains);
        sql = DBUtil.getCountSql(sql);
        return total(sql.toString(),paramValues.toArray());
    }

    /**
     * 分页查询,根据entityClass返回值
     * @param entityClass
     * @param pageInfo
     * @param sql
     * @param paramValues
     * @param <T>
     * @return
     */
    public <T> PageInfo<T> queryPageBySql(Class<T> entityClass,String sql,PageInfo<T> pageInfo,Object... paramValues){
        String sqlCount = DBUtil.getCountSql(sql);
        String sqlPage = DBUtil.getLimitString(sql,pageInfo.getPage(),pageInfo.getPageSize());
        pageInfo.setTotal(total(sqlCount,paramValues));
        List<T> data = queryBySql(entityClass,sqlPage,paramValues);
        pageInfo.setData(data);
        return pageInfo;
    }


    public <T> PageInfo<T> queryPage(Class<T> entityClass,PageInfo<T> pageInfo,Restrain... restrains){
        List paramValues = new ArrayList();
        String sql = DBUtil.transSqlWhere(entityClass,paramValues,restrains);
        return queryPageBySql(entityClass,sql,pageInfo,paramValues.toArray());
    }

    public <T> T queryById(Class<T> entityClass, int id) {
        List<T> list = query(entityClass, Restrain.eq("id", id));
        if (list.size() > 0) {
            return list.get(0);
        }
        return null;
    }



    //endregion 数据库扩展方法


    //region 数据库扩展更新方法


    /**
     * 将对象添加到数据库中
     * @param entity
     */
    public void add(Object entity,Boolean autoSetId) {
        String tableName = DBUtil.getTableName(entity.getClass());
        add(tableName,entity,autoSetId);
    }

    public void add(String tableName,Object entity){
        add(tableName,entity,false);
    }
    public void add(String tableName,Object entity,Boolean autoSetId){
        try {
            StringBuilder sql1 = new StringBuilder();
            List<Object> paramValues = new ArrayList<Object>();
            Method[] methods = entity.getClass().getMethods();
            for (Method method : methods) {
                if (method.getName().toLowerCase().equals("getid") ||method.getName().toLowerCase().equals("getclass") || !method.getName().startsWith("get") || method.getParameterTypes().length != 0) {
                    continue;
                }
                Object obj = method.invoke(entity);
                if (obj == null) {
                    continue;
                }
                if (sql1.length() > 0) {
                    sql1.append(",");
                }
                sql1.append(method.getName().substring(3));
                paramValues.add(obj);
            }
            if (paramValues.size() == 0) {
                throw new EasySqlException("对象中不包含任何数据");
            }
            StringBuilder sql2 = new StringBuilder("?");
            for (int i = 1; i < paramValues.size(); i++) {
                sql2.append(",?");
            }
            String sql = "insert into " + tableName + "(" + sql1 + ") values(" + sql2 + ")";
            if(autoSetId){
                try{
                    execute(sql,paramValues.toArray());
                    String sql_autoIncreSql = this.AutoIncreSql(tableName);
                    Object id = Integer.parseInt(queryScalar(sql_autoIncreSql).toString());
                    SetIdValue(entity,id);
                }catch (Exception ex){
                    throw new EasySqlException(ex);
                }
            }else{
                execute(sql, paramValues.toArray());
            }
        }catch (Exception ex){
            throw new EasySqlException(ex);
        }
    }

    private void SetIdValue(Object model, Object value)
    {
        if (model == null)
        {
            return;
        }

        Method[] ps = model.getClass().getMethods();
        for(Method p : ps){
            if(p.getName().equals("setId")){
                try {
                    p.invoke(model,value);
                } catch (Exception e) {
                    throw new EasysqlException(e);
                }
                return;
            }
        }

    }

    public void add(Object entity){
        add(entity,false);
    }
    public void add_autoSetId(Object entity){
        add(entity,true);
    }

    /**
     * 添加对象到数据库中,然后根据restrains查询出数量,如果数量大于maxCount,则返回false
     * @param entity
     * @param maxCount
     * @param restrains
     * @return
     */
    public <T> boolean add(T entity,int maxCount,Restrain... restrains){
        try{
            beginTrans();
            add(entity);
            int total = total(entity.getClass(),restrains);
            if(total>maxCount){
                rollback();
                return false;
            }
            commitTrans();
            return true;
        }catch (Exception ex){
            rollback();
            throw new EasysqlException(ex);
        }
    }

    public <T> void modify(T entity){
        modifys(entity,Restrain.eq("id",DBUtil.readId(entity)));
    }
    /**
     * 根据entity的id修改对象到数据库中,然后根据restrains查询出数量,如果数量大于maxCount,则返回false
     * @param entity
     * @param maxCount
     * @param restrains
     * @param <T>
     * @return
     */
    public <T> boolean modify(T entity, int maxCount, Restrain[] restrains) {
        try{
            beginTrans();
            modify(entity);
            int total = total(entity.getClass(),restrains);
            if(total>maxCount){
                rollback();
                return false;
            }
            commitTrans();
            return true;
        }catch (Exception ex){
            rollback();
            throw new EasysqlException(ex);
        }
    }

    public <T> int modifys(T entity,Restrain... restrains){
        try {
            String tableName = DBUtil.getTableName(entity.getClass());
            StringBuilder sql1 = new StringBuilder();
            List<Object> paramValues = new ArrayList<Object>();
            Method[] methods = entity.getClass().getMethods();
            for (Method method : methods) {
                // Class[] cs = new Class[]{String.class,Integer.class, Timestamp.class,Boolean.class,Long.class};
                if(method.getName().toLowerCase().equals("getid")){
                    continue;
                }
                if (method.getName().toLowerCase().equals("getclass") || !method.getName().startsWith("get") || method.getParameterTypes().length != 0) {
                    continue;
                }
                Object obj = method.invoke(entity);
                if (obj == null) {
                    continue;
                }
                //Class<?> returnType = method.getReturnType();
                if (sql1.length() > 0) {
                    sql1.append(",");
                }
                sql1.append(method.getName().substring(3)+"=?");
                paramValues.add(obj);
            }
            if (paramValues.size() == 0) {
                throw new EasySqlException("对象中不包含任何数据");
            }
            StringBuilder sql = new StringBuilder("update " + tableName + " set "+sql1 +" where 1=1 ");
            DBUtil.transSqlWhere(sql,paramValues,restrains);
            return execute(sql.toString(), paramValues.toArray());
        }catch (Exception ex){
            throw new EasySqlException(ex);
        }
    }

    public int delById(Class<?> entityClass, int id) {
        String tableName = DBUtil.getTableName(entityClass);
        String sql = "delete from "+tableName+" where id="+id;
        return execute(sql);
    }



    //endregion 数据库扩展更新方法



}
